package com.xbs.dao.selfdriving;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.sun.rowset.internal.Row;
import com.xbs.dao.conn.BaseDao;
import com.xbs.dao.conn.RowDatas;
import com.xbs.entity.CarEntity;
/**
 * 后台自驾租车订单管理DAO
 * @author 余凯
 *
 */
import com.xbs.entity.CommonOrderEntity;
import com.xbs.entity.DetailsAdminEntity;
import com.xbs.entity.PagesEntity;
import com.xbs.entity.selfOrderAdminEntity;
import com.xbs.utils.StringUtils;

public class selfDrivingOrderAdmin extends BaseDao {
	/**
	 * 实例化一个后台自驾租车订单对象
	 */
	private RowDatas<selfOrderAdminEntity> osae;
	/**
	 * 实例化一个int类型
	 * 
	 */
	private RowDatas<Integer> total;
	/**
	 * 实例化一个查看详情对象
	 */
	private RowDatas<DetailsAdminEntity> detail;
	private RowDatas<String> str;
	private RowDatas<CommonOrderEntity> comm;
	private RowDatas<CarEntity> car;
	public selfDrivingOrderAdmin() {
		osae = new getComm();
		total = new getTotal();
		detail = new getDetail();
		str = new getStr();
		comm = new getCom();
		car =new getCar();
	}
	
	/**
	 * 1.默认查询当天生成的订单
	 * @return
	 */
	public List<selfOrderAdminEntity> selALLco(String fromdate,String todate,String tel,int currpage,int pagesize){
		StringBuffer sb = new StringBuffer("select tb_common_order.co_id,co_order_status,co_plan_pay_money,co_fact_pay_money,co_remark,co_first_pay_money,rent_telphone,rent_name,order_guarantee_money from tb_common_order,tb_rent,tb_order_money"
				+ " where tb_common_order.co_id=tb_rent.co_id "
				+ " and tb_common_order.co_id=tb_order_money.order_id "
				+ " and tb_common_order.co_id in "
				+ " (select time_order_id from tb_time where DATE_FORMAT(time_order_submit,'%Y-%m-%d') between '"+fromdate+"' and '"+todate+"')");
		if(StringUtils.isEmpty(tel)){
			sb.append(" and rent_telphone='"+tel+"'");
		}
		sb.append(" limit "+currpage+","+pagesize+"");
		String sql = sb.toString();
		return queryList(sql, osae);
	}
	
	/**
	 * 2.获取总记录数
	 * @author 余凯
	 *
	 */
	
	public int selOrderAdminEntityTotal(String fromdate,String todate,String tel){
		StringBuffer sb = new StringBuffer("select count(*) from tb_common_order,tb_rent "
				+ "where tb_common_order.co_id=tb_rent.co_id "
				+ "and tb_common_order.co_id in "
				+ "(select time_order_id from tb_time where DATE_FORMAT(time_order_submit,'%Y-%m-%d') between '"+fromdate+"' and '"+todate+"')");
		
		if(StringUtils.isEmpty(tel)){
			sb.append(" and rent_telphone='"+tel+"'");
		}
		String sql = sb.toString();
		return query(sql, total);
	}
	
	
	/**
	 * 3.根据订单编号更改订单状态
	 * @author 余凯
	 *
	 */
	
	public int cancelOrder(String id,String status){
		String sql = "update  tb_common_order SET co_order_status='"+status+"' where co_id='"+id+"'";
		return updateExecuted(sql);
	}
	
	/**
	 * 4.根据订单编号，更新实收金额
	 * @param id
	 * @return
	 */
	public int upFactMoney(String id,Double money){
		String sql = "update  tb_common_order SET co_fact_pay_money="+money+"  where co_id='"+id+"'";
		return updateExecuted(sql);
	}
	
	/**
	 * 5.根据订单编号，更新实付金额和实收金额
	 * @param id
	 * @param money
	 * @return
	 */
	public int upMoney(String id,Double money){
		String sql = "update tb_common_order set co_fact_pay_money="+money+",co_first_pay_money="+money+" where co_id='"+id+"'";
		return updateExecuted(sql);
	}
	
	/**
	 * 6.根据订单编号，修改订单支付时间
	 * @param id
	 * @param time
	 * @return
	 */
	public int upPayTime(String id,String time){
		String sql = "update tb_time set time_order_pay='"+time+"' where time_order_id='"+id+"'";
		return updateExecuted(sql);
	}
	
	/**
	 * 7.根据订单编号，修改订单取消时间
	 * @param id
	 * @param time
	 * @return
	 */
	public int upCancelTime(String id,String time){
		String sql = "update tb_time set time_order_cancel='"+time+"' where time_order_id='"+id+"'";
		return updateExecuted(sql);
	}
	
	/**
	 * 8.根据订单编号，更新用户所驾驶的车牌号
	 * @param id
	 * @param carnum
	 * @return
	 */
	public int upCarId(String id,String carnum){
		String sql = "update tb_common_order set co_card_id='"+carnum+"' where co_id='"+id+"'";
		return updateExecuted(sql);
	}
	
	/**
	 * 9.根据订单编号，修改订单完成时间
	 * @param id
	 * @param time
	 * @return
	 */
	public int upFinishTime(String id,String time){
		String sql = "update tb_time set time_order_finish='"+time+"' where time_order_id='"+id+"'";
		return updateExecuted(sql);
	}
	
	/**
	 * 10.根据订单编号，更新备注
	 * @param id
	 * @param carnum
	 * @return
	 */
	public int upRemark(String id,String remark){
		String sql = "update tb_common_order set co_remark='"+remark+"' where co_id='"+id+"'";
		return updateExecuted(sql);
	}
	
	/**
	 * 11.更新订单金额表中的车辆破损费
	 * @param id
	 * @param money
	 * @return
	 */
	public int upDamageMoney(String id,Double money){
		String sql = "update tb_order_money set order_car_damage_money="+money+" where order_id='"+id+"' ";
		return updateExecuted(sql);
	}
	
	/**
	 * 12.更新订单表中的客户赔偿金额（车辆破损费）
	 * @param id
	 * @param money
	 * @return
	 */
	public int upCompensation(String id,Double money){
		String sql = "update tb_common_order set co_compensation="+money+" where co_id='"+id+"' ";
		return updateExecuted(sql);
	}
	
	/**
	 * 13.查看详情界面显示内容(上门取送)
	 * @param id
	 * @return
	 */
	public DetailsAdminEntity selDetail(String id){
		String sql = "select tb_common_order.co_id,"
				+ " (select car_model_image from tb_car_model where car_model_id in (select co_car_type from tb_common_order where co_id='"+id+"')) as car_model_image,"
				+ " (select car_model_name from tb_car_model where car_model_id in (select co_car_type from tb_common_order where co_id='"+id+"')) as car_model_name,"
				+ " rent_name,rent_telphone,"
				+ " co_card_id,co_order_status,co_get_kind,"
				+ " (select time_plan_get from tb_time where time_order_id='"+id+"') as time_plan_get,"
				+ " (select time_plan_return from tb_time where time_order_id='"+id+"') as time_plan_return,"
				+ " (select time_order_submit from tb_time where time_order_id='"+id+"') as time_order_submit,"
				+ " (select time_order_pay from tb_time where time_order_id='"+id+"') as time_order_pay,"
				+ " (select time_order_finish from tb_time where time_order_id='"+id+"') as time_order_finish,"
				+ " (select time_order_cancel from tb_time where time_order_id='"+id+"') as time_order_cancel,"
				+ " co_get_address,co_return_address,"
				+ " (select order_rent_money from tb_order_money where order_id='"+id+"') as order_rent_money,"
				+ " (select order_guarantee_money from tb_order_money where order_id='"+id+"') as order_guarantee_money,"
				+ " (select order_insurance_money from tb_order_money where order_id='"+id+"') as order_insurance_money,"
				+ " (select order_service_money from tb_order_money where order_id='"+id+"') as order_service_money,"
				+ " (select order_car_damage_money from tb_order_money where order_id='"+id+"') as order_car_damage_money,"
				+ " co_plan_pay_money,co_fact_pay_money,co_return_money,"
				+ " (select relet_end_time from tb_relet where relet_id='"+id+"') as relet_end_time,"
				+ " (select relet_return_time from tb_relet where relet_id='"+id+"') as relet_return_time, "
				+ " (select relet_plan_money from tb_relet where relet_id='"+id+"') as relet_plan_money"
				+ " from tb_common_order,tb_rent where tb_common_order.co_id='"+id+"' and tb_common_order.co_id=tb_rent.co_id";
		return query(sql, detail);
	}
	
	/**
	 * 14.查看详情界面显示内容(到店取还(包含异店还车))
	 * @param id
	 * @return
	 */
	public DetailsAdminEntity selDetailStore(String id){
		String sql = "select tb_common_order.co_id,"
				+ " (select car_model_image from tb_car_model where car_model_id in (select co_car_type from tb_common_order where co_id='"+id+"')) as car_model_image,"
				+ " (select car_model_name from tb_car_model where car_model_id in (select co_car_type from tb_common_order where co_id='"+id+"')) as car_model_name,"
				+ " rent_name,rent_telphone,"
				+ " co_card_id,co_order_status,co_get_kind,"
				+ " (select time_plan_get from tb_time where time_order_id='"+id+"') as time_plan_get,"
				+ " (select time_plan_return from tb_time where time_order_id='"+id+"') as time_plan_return,"
				+ " (select time_order_submit from tb_time where time_order_id='"+id+"') as time_order_submit,"
				+ " (select time_order_pay from tb_time where time_order_id='"+id+"') as time_order_pay,"
				+ " (select time_order_finish from tb_time where time_order_id='"+id+"') as time_order_finish,"
				+ " (select time_order_cancel from tb_time where time_order_id='"+id+"') as time_order_cancel,"
				+ " (select store_name from tb_store where store_id in (SELECT co_get_store from tb_common_order where co_id='"+id+"'))  as co_get_address,"
				+ " (select store_name from tb_store where store_id in (SELECT co_return_store from tb_common_order where co_id='"+id+"')) as co_return_address,"
				+ " (select order_rent_money from tb_order_money where order_id='"+id+"') as order_rent_money,"
				+ " (select order_guarantee_money from tb_order_money where order_id='"+id+"') as order_guarantee_money,"
				+ " (select order_insurance_money from tb_order_money where order_id='"+id+"') as order_insurance_money,"
				+ " (select order_service_money from tb_order_money where order_id='"+id+"') as order_service_money,"
				+ " (select order_car_damage_money from tb_order_money where order_id='"+id+"') as order_car_damage_money,"
				+ " co_plan_pay_money,co_fact_pay_money,co_return_money,"
				+ " (select relet_end_time from tb_relet where relet_id='"+id+"') as relet_end_time,"
				+ " (select relet_return_time from tb_relet where relet_id='"+id+"') as relet_return_time, "
				+ " (select relet_plan_money from tb_relet where relet_id='"+id+"') as relet_plan_money"
				+ " from tb_common_order,tb_rent where tb_common_order.co_id='"+id+"' and tb_common_order.co_id=tb_rent.co_id";
		return query(sql, detail);
	}
	
	/**
	 * 15.判断送车方式
	 * @param id
	 * @return
	 */
	public String getStatus(String id){
		String sql = "select co_get_kind from tb_common_order where co_id='"+id+"'";
		return query(sql, str);
	}
	
	/**
	 * 16.更新应退金额
	 * @param id
	 * @param money
	 * @return
	 */
	public int upReturnMoney(String id,Double money){
		String sql="update tb_common_order set co_return_money="+money+" where co_id='"+id+"'";
		return updateExecuted(sql);
	}
	
	
	/**
	 * 17.根据订单编号查询（是否为异地还车）
	 * @param order
	 * @return
	 */
	
	public CommonOrderEntity selAddress(String order){
		String sql = "select * from tb_common_order where co_id='"+order+"'";
		return query(sql, comm);
	}
	
	/**
	 * 18.将异地还车的车牌号，初始门店，还车门店写入顺风车
	 * @param che
	 * @param fromnum
	 * @param tonum
	 * @return
	 */
	public int insHitch(String che,int fromnum,int tonum,String status){
		String sql = "insert into tb_hitchhiking (hitch_id,hitch_status,hitch_num,hitch_return_num) values ('"+che+"','"+status+"',"+tonum+","+fromnum+")";
		return updateExecuted(sql);
	}
	
	
	/**
	 * 19.根据车型获取可租的具体车辆车牌号(状态为可租的)
	 * @param order
	 * @return
	 */
	public List<CarEntity> selCarId(String order){
		String sql="select car_id from tb_car where car_model_id in (select co_car_type from tb_common_order where co_id='"+order+"') and car_status='0'";
		return queryList(sql, car);
	}
	
	/**
	 * 20.根据车牌号，修改车的状态
	 * @param carnum
	 * @return
	 */
	public int upCarNum(String carnum,int status){
		String sql = "update tb_car set car_status="+status+" where car_id='"+carnum+"'";
		return updateExecuted(sql);
	}
	
	/**
	 * 21.查询该订单的车牌号
	 * @param num
	 * @return
	 */
	public String selCarNum(String num){
		String sql = "select co_card_id from tb_common_order where co_id='"+num+"'";
		return query(sql, str);
	}
	
	public class getComm implements RowDatas<selfOrderAdminEntity>{
		@Override
		public selfOrderAdminEntity getRowData(ResultSet rs) throws SQLException {
			selfOrderAdminEntity osae = new selfOrderAdminEntity();
			osae.setId(rs.getString("co_id"));
			osae.setStatus(rs.getString("co_order_status"));
			osae.setPmoney(rs.getDouble("co_plan_pay_money"));
			osae.setFmoney(rs.getDouble("co_fact_pay_money"));
			osae.setYmoney(rs.getDouble("order_guarantee_money"));
			if(rs.getString("co_remark")==null||rs.getString("co_remark").length()==0){
				osae.setRemark("");
			}else{
				osae.setRemark(rs.getString("co_remark"));
			}
			osae.setMoney(rs.getDouble("co_first_pay_money"));
			osae.setTel(rs.getString("rent_telphone"));
			osae.setName(rs.getString("rent_name"));
			return osae;
		}
	}
	
	public class getTotal implements RowDatas<Integer>{
		@Override
		public Integer getRowData(ResultSet rs) throws SQLException {
			return rs.getInt(1);
		}
	}
	
	public class getDetail implements RowDatas<DetailsAdminEntity>{
		@Override
		public DetailsAdminEntity getRowData(ResultSet rs) throws SQLException {
			DetailsAdminEntity de = new DetailsAdminEntity();
			de.setId(rs.getString("co_id"));
			de.setUrl(rs.getString("car_model_image"));
			de.setName(rs.getString("rent_name"));
			de.setTel(rs.getString("rent_telphone"));
			de.setType(rs.getString("car_model_name"));
			if(rs.getString("co_card_id")==null||rs.getString("co_card_id").length()==0){
				de.setCard("");
			}else{
				de.setCard(rs.getString("co_card_id"));
			}
			
			de.setStatus(rs.getString("co_order_status"));
			de.setKind(rs.getString("co_get_kind"));
			de.setGettime(rs.getString("time_plan_get").substring(0, 19));
			de.setReturntime(rs.getString("time_plan_return").substring(0,19));
			de.setGetaddress(rs.getString("co_get_address"));
			de.setReturnaddress(rs.getString("co_return_address"));
			de.setRent(rs.getDouble("order_rent_money"));
			de.setGuarantee(rs.getDouble("order_guarantee_money"));
			de.setInsurance(rs.getDouble("order_insurance_money"));
			de.setDamage(rs.getDouble("order_car_damage_money"));
			de.setPlan(rs.getDouble("co_plan_pay_money"));
			de.setFact(rs.getDouble("co_fact_pay_money"));
			de.setTui(rs.getDouble("co_return_money"));
			de.setPei(rs.getDouble("order_service_money"));
			de.setSubtime(rs.getString("time_order_submit").substring(0,19));
			if(rs.getString("time_order_pay")==null||rs.getString("time_order_pay").length()==0){
				de.setFutime("");
			}else{
				de.setFutime(rs.getString("time_order_pay").substring(0, 19));
			}
			if(rs.getString("time_order_finish")==null||rs.getString("time_order_finish").length()==0){
				de.setFintime("");
			}else{
				de.setFintime(rs.getString("time_order_finish").substring(0, 19));
			}
			if(rs.getString("time_order_cancel")==null||rs.getString("time_order_cancel").length()==0){
				de.setCantime("");
			}else{
				de.setCantime(rs.getString("time_order_cancel").substring(0, 19));
			}
			
			
			if(rs.getString("relet_end_time")==null||rs.getString("relet_end_time").length()==0){
				de.setRestarttime("");
			}else{
				de.setRestarttime(rs.getString("relet_end_time"));
			}
			if(rs.getString("relet_return_time")==null||rs.getString("relet_return_time").length()==0){
				de.setReendtime("");
			}else{
				de.setReendtime(rs.getString("relet_return_time"));
			}
			de.setReletmoney(rs.getDouble("relet_plan_money"));
			return de;
		}
	}
	
	public class getStr implements RowDatas<String>{
		@Override
		public String getRowData(ResultSet rs) throws SQLException {
			return rs.getString(1);
		}
	}
	
	public class getCom implements RowDatas<CommonOrderEntity>{
		@Override
		public com.xbs.entity.CommonOrderEntity getRowData(ResultSet rs) throws SQLException {
			CommonOrderEntity comm = new CommonOrderEntity();
			comm.setCo_get_kind(rs.getString("co_get_kind"));
			comm.setCo_car_id(rs.getString("co_card_id"));
			
			if(rs.getString("co_get_store")==null||rs.getString("co_get_store").length()==0){
				comm.setCo_get_store("");
			}else{
				comm.setCo_get_store(rs.getString("co_get_store"));
			}
			
			if(rs.getString("co_return_store")==null||rs.getString("co_return_store").length()==0){
				comm.setCo_return_store("");
			}else{
				comm.setCo_return_store(rs.getString("co_return_store"));
			}
			
			return comm;
		}
		
	}
	
	public class getCar implements RowDatas<CarEntity>{
		@Override
		public CarEntity getRowData(ResultSet rs) throws SQLException {
			CarEntity car = new CarEntity();
			car.setCarid(rs.getString("car_id"));
			return car;
		}
	}
}
